package secondary.employmentlaw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ontology.EmploymentLawOntology;
import ontology.UMCourtLOntology;
import ontology.UMCourtLVocabulary;
import ontology.actions.general.GetCase;
import ontology.actions.general.GetCases;
import ontology.actions.general.Index;
import ontology.actions.general.InformCase;
import ontology.actions.general.Log;
import ontology.actions.general.ParseCases;
import ontology.actions.general.ReturnList;
import ontology.actions.general.ReturnParseCases;
import ontology.concepts.employmentlaw.Case;
import ontology.concepts.employmentlaw.Norm;
import ontology.concepts.general.DBQueryRules;

import classes.MessageTemplates;
import classes.UMCourtAgent;
import jade.content.Concept;
import jade.content.ContentElement;
import jade.content.lang.Codec;
import jade.content.lang.Codec.CodecException;
import jade.content.lang.sl.SLCodec;
import jade.content.onto.Ontology;
import jade.content.onto.OntologyException;
import jade.content.onto.UngroundedException;
import jade.content.onto.basic.Action;
import jade.core.behaviours.CyclicBehaviour;
import jade.domain.DFService;
import jade.lang.acl.ACLMessage;
import jade.util.leap.ArrayList;
import jade.util.leap.List;

public class DatabaseL extends UMCourtAgent implements UMCourtLVocabulary {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 464937464163563007L;
	private static Connection C;
	private static Statement Stmt;
	private final MessageTemplates mt = new MessageTemplates();
	private Codec codec = new SLCodec();
	private Ontology ontology = EmploymentLawOntology.getInstance();
	
	@Override
	protected void setup() 
	{
		System.out.println(getDate()+ ": "+this.getLocalName()+" agent is now running...");
		registerAgent((String)getArguments()[0]);
		
		// Register language and ontology
	    getContentManager().registerLanguage(codec);
	    getContentManager().registerOntology(ontology);
		
		C = connectDB();
		System.out.println(getDate()+" : Connected to Database");
		
		this.addBehaviour(new receiveBehaviour());
	}
	
	
	@Override
	protected void takeDown()
	{
		try 
		{
			Stmt = C.createStatement();
			Stmt.executeUpdate("INSERT into UMCourtL.log(time, agent_name, event) VALUES ('"+System.currentTimeMillis()+"','"+this.getLocalName()+"','UMCourt agent platform is shutting down')");
		} 
		catch (SQLException e){e.printStackTrace();}
		
		System.out.println(getDate()+": "+this.getLocalName() +" is de-registering...");
		try { DFService.deregister(this); }
        catch (Exception e) {}
	}
	
	//behaviours
	class receiveBehaviour extends CyclicBehaviour
	{
		@Override
		public void action() 
		{
			ACLMessage msg = receive();
	        if (msg == null) 
	        { 
	        	block(); 
	        	return; 
	        }
	        
	        try 
			{
	        	ContentElement content = getContentManager().extractContent(msg);
		        Concept action = ((Action)content).getAction();
         		
		        switch (msg.getPerformative()) 
		        {
		        case (ACLMessage.REQUEST):
		        	if (action instanceof ParseCases)
			        {
			        	content = getContentManager().extractContent(msg);
			            ParseCases pc = (ParseCases)((Action)content).getAction();
			            
			            ArrayList caseids = (ArrayList) pc.getCaseids();
			            ArrayList cases = new ArrayList();
			            for (int i=0;i<caseids.size();i++)
			            {
			            	String id = (String) caseids.get(i);
			            	//Get party information
			        		Statement select = C.createStatement();
						    ResultSet result = select.executeQuery("SELECT party1id, party2id, party1wins FROM umCourtl.casebase WHERE caseid = '"+ id +"'");
						    result.next();
						    String party1 = result.getString(1);
						    String party2 = result.getString(2);
						    Boolean party1wins = result.getBoolean(3);
						    
						    //Get norms information
						    select = C.createStatement();
						    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ id +"' AND partyid = '"+party1+"'");
						    List norms1 = new ArrayList();
						    while (result.next())
						    {
						    	Norm n = new Norm();
						    	n.setArticle(""+result.getInt(1));
						    	n.setItem(result.getString(2));
						    	n.setNumber(""+result.getInt(3));
						    	norms1.add(n);
						    }
						    select = C.createStatement();
						    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ id +"' AND partyid = '"+party2+"'");
						    List norms2 = new ArrayList();
						    while (result.next())
						    {
						    	Norm n = new Norm();
						    	n.setArticle(""+result.getInt(1));
						    	n.setItem(result.getString(2));
						    	n.setNumber(""+result.getInt(3));
						    	norms2.add(n);
						    }
						    
						    //fill case information
						    Case c = new Case();
						    c.setID(id);
						    c.setEmployeeID(party1);
						    c.setEmployerID(party2);
						    c.setEmployeeNorms(norms1);
						    c.setEmployerNorms(norms2);
						    c.setParty1wins(party1wins);
						    cases.add(c);
			            }
			            
			            ReturnParseCases rpc = new ReturnParseCases();
			            rpc.setList(cases);
			            ACLMessage reply = new ACLMessage(ACLMessage.INFORM);
			            reply.setLanguage(codec.getName());
			            reply.setOntology(ontology.getName());
			            reply.setConversationId(msg.getConversationId());
						
						try 
						{
							getContentManager().fillContent(reply, new Action(msg.getSender(), rpc));
							reply.addReceiver(msg.getSender());
							send(reply);
						}
						catch (Exception ex) { ex.printStackTrace(); }
			        }		        	
		        	if (action instanceof Index)
			        {
			        	content = getContentManager().extractContent(msg);
			            Index i = (Index)((Action)content).getAction();
			           
			            //TODO: Process the operation
			            System.out.println("***"+i.toString());
			            Stmt = C.createStatement();
						//Stmt.executeUpdate("INSERT into UMCourtL.log(time, agent_name, event) VALUES ('"+System.currentTimeMillis()+"','"+msg.getSender().getLocalName()+"','"+msg.getContent()+"')");
			        }
		        	if (action instanceof Log)
		        	{
		        		content = getContentManager().extractContent(msg);
			            Log l = (Log)((Action)content).getAction();
			            Stmt = C.createStatement();
						Stmt.executeUpdate("INSERT into UMCourtL.log(time, agent_name, event) VALUES ('"+l.getTime()+"','"+msg.getSender().getLocalName()+"','"+l.getMessage()+"')");
		        	}
		        	if (action instanceof GetCases)
		        	{
		        		content = getContentManager().extractContent(msg);
			            GetCases gcs = (GetCases)((Action)content).getAction();
			            
			            myprintln("Received request to query database from "+msg.getSender().getName());
			            Case newCase = (Case) gcs.getC();
			            String caseID = newCase.getID();
			            DBQueryRules r = gcs.getR();
			            String depth = r.getDepth();
			            List caseIDs = new ArrayList(); //List of caseIDs
			            
			            //get norms addressed by employee
			            List l1 = newCase.getEmployeeNorms(); //party1
			            List l2 = newCase.getEmployerNorms(); //party2	
					    
			            if (depth.equals("article"))
			            {
			            	//select all cases in which employee or employer addresses a norm in common
			            	for (int i=0;i<l1.size();i++)
			            	{
			            		String article = ((Norm) l1.get(i)).getArticle();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid FROM umCourtl.normscase WHERE article = '"+article+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
				            		ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party1"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            	for (int i=0;i<l2.size();i++)
			            	{
			            		String article = ((Norm) l2.get(i)).getArticle();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid  FROM umCourtl.normscase WHERE article = '"+article+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
			            			ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party2"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            }
			            
			            if (depth.equals("number"))
			            {
			            	//select all cases in which employee or employer addresses a norm in common
			            	for (int i=0;i<l1.size();i++)
			            	{
			            		String article = ((Norm) l1.get(i)).getArticle();
			            		String number = ((Norm) l1.get(i)).getNumber();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid FROM umCourtl.normscase WHERE article = '"+article+"' AND number = '"+number+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
				            		ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party1"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            	for (int i=0;i<l2.size();i++)
			            	{
			            		String article = ((Norm) l2.get(i)).getArticle();
			            		String number = ((Norm) l2.get(i)).getNumber();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid  FROM umCourtl.normscase WHERE article = '"+article+"' AND number = '"+number+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
			            			ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party2"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            }
			            
			            if (depth.equals("item"))
			            {
			            	//select all cases in which employee or employer addresses a norm in common
			            	for (int i=0;i<l1.size();i++)
			            	{
			            		String article = ((Norm) l1.get(i)).getArticle();
			            		String number = ((Norm) l1.get(i)).getNumber();
			            		String item = ((Norm) l1.get(i)).getItem();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid FROM umCourtl.normscase WHERE article = '"+article+"' AND number = '"+number+"' AND item = '"+item+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
				            		ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party1"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            	for (int i=0;i<l2.size();i++)
			            	{
			            		String article = ((Norm) l2.get(i)).getArticle();
			            		String number = ((Norm) l2.get(i)).getNumber();
			            		String item = ((Norm) l2.get(i)).getItem();
			            		Statement select = C.createStatement();
			            		ResultSet result = select.executeQuery("SELECT DISTINCT caseid, partyid  FROM umCourtl.normscase WHERE article = '"+article+"' AND number = '"+number+"' AND item = '"+item+"'");
			            		
			            		while (result.next())
			            		{
			            			String caseID2 = result.getString(1);
			            			select = C.createStatement();
			            			ResultSet result2 = select.executeQuery("SELECT role FROM umCourtl.roles WHERE partyid = '"+result.getString(2)+"'");
				            		result2.next();
				            		if (result2.getString(1).equals("party2"))
				            			if (!caseIDs.contains(caseID2))
				            				caseIDs.add(caseID2);
			            		}
			            	}
			            }
			            
			            //Parse all cases to send in the return list
		            	ReturnList rl = new ReturnList();
		            	ArrayList cases = new ArrayList();
		            	
		            	for (int i=0;i<caseIDs.size();i++ )
		            	{
		            		String cid = (String) caseIDs.get(i);
		            		
		            		//Get party information
			        		Statement select = C.createStatement();
						    ResultSet result = select.executeQuery("SELECT party1id, party2id FROM umCourtl.casebase WHERE caseid = '"+ cid +"'");
						    result.next();
						    String party1 = result.getString(1);
						    String party2 = result.getString(2);
						    
						    //Get norms information
						    select = C.createStatement();
						    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ cid +"' AND partyid = '"+party1+"'");
						    List norms1 = new ArrayList();
						    while (result.next())
						    {
						    	Norm n = new Norm();
						    	n.setArticle(""+result.getInt(1));
						    	n.setItem(result.getString(2));
						    	n.setNumber(""+result.getInt(3));
						    	norms1.add(n);
						    }
						    select = C.createStatement();
						    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ cid +"' AND partyid = '"+party2+"'");
						    List norms2 = new ArrayList();
						    while (result.next())
						    {
						    	Norm n = new Norm();
						    	n.setArticle(""+result.getInt(1));
						    	n.setItem(result.getString(2));
						    	n.setNumber(""+result.getInt(3));
						    	norms2.add(n);
						    }
						    
						    select = C.createStatement();
							result = select.executeQuery("SELECT party1wins FROM umCourtl.casebase WHERE caseid = '"+cid+"'");
							result.next();
							int party1wins = result.getInt(1);
							
						    //fill case information
						    Case c = new Case();
						    c.setID(cid);
						    c.setEmployeeID(party1);
						    c.setEmployerID(party2);
						    c.setEmployeeNorms(norms1);
						    c.setEmployerNorms(norms2);
						    c.setParty1wins(party1wins == 1);
						    
						    cases.add(c);
		            	}
		            	
		            	rl.setList(cases);
		            	
		            	//Return List of cases
		            	ACLMessage reply = new ACLMessage(ACLMessage.INFORM);
		    			reply.setLanguage(codec.getName());
		    			reply.setOntology(ontology.getName());
		    			reply.setConversationId(msg.getConversationId());
		    			
		    			try 
		    			{
		    				getContentManager().fillContent(reply, new Action(msg.getSender(), rl));
		    				reply.addReceiver(msg.getSender());
		    				myprintln("Returning query result...");
		    				send(reply);
		    			}
		    			catch (Exception ex) { ex.printStackTrace(); }
			            
			            
			            
		        	}
		        	if (action instanceof GetCase)
		        	{
		        		content = getContentManager().extractContent(msg);
		        		String id = ((GetCase)((Action)content).getAction()).getId();
		        		
		        		//Get party information
		        		Statement select = C.createStatement();
					    ResultSet result = select.executeQuery("SELECT party1id, party2id FROM umCourtl.casebase WHERE caseid = '"+ id +"'");
					    result.next();
					    String party1 = result.getString(1);
					    String party2 = result.getString(2);
					    
					    //Get norms information
					    select = C.createStatement();
					    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ id +"' AND partyid = '"+party1+"'");
					    List norms1 = new ArrayList();
					    while (result.next())
					    {
					    	Norm n = new Norm();
					    	n.setArticle(""+result.getInt(1));
					    	n.setItem(result.getString(2));
					    	n.setNumber(""+result.getInt(3));
					    	norms1.add(n);
					    }
					    select = C.createStatement();
					    result = select.executeQuery("SELECT article, item, number FROM umCourtl.normscase WHERE caseid = '"+ id +"' AND partyid = '"+party2+"'");
					    List norms2 = new ArrayList();
					    while (result.next())
					    {
					    	Norm n = new Norm();
					    	n.setArticle(""+result.getInt(1));
					    	n.setItem(result.getString(2));
					    	n.setNumber(""+result.getInt(3));
					    	norms2.add(n);
					    }
					    
					    //fill case information
					    Case c = new Case();
					    c.setID(id);
					    c.setEmployeeID(party1);
					    c.setEmployerID(party2);
					    c.setEmployeeNorms(norms1);
					    c.setEmployerNorms(norms2);
					    
					    InformCase ic = new InformCase();
					    ic.setC(c);
					    //return the case
					    ACLMessage reply = new ACLMessage(ACLMessage.INFORM);
						reply.setLanguage(codec.getName());
						reply.setOntology(ontology.getName());
						try 
						{
							getContentManager().fillContent(reply, new Action(msg.getSender(), ic));
							reply.addReceiver(msg.getSender());
							send(reply);
						}
						catch (Exception ex) { ex.printStackTrace(); }
						
		        	}
					
					
		        }
			} 
			catch (SQLException e){e.printStackTrace();} 
			catch (UngroundedException e) {e.printStackTrace();} 
			catch (CodecException e) {e.printStackTrace();} 
			catch (OntologyException e) {e.printStackTrace();}
			
			
			
			
			//TODO: convert to action
			/*
			//IT IS A LOG MESSAGE
			ACLMessage msg= receive(mt.LOG);
            if (msg!=null)
            {
    			try 
    			{
    				Stmt = C.createStatement();
					Stmt.executeUpdate("INSERT into UMCourtL.log(time, agent_name, event) VALUES ('"+System.currentTimeMillis()+"','"+msg.getSender().getLocalName()+"','"+msg.getContent()+"')");
				} 
    			catch (SQLException e){e.printStackTrace();}
            }
            block();
            */
		}
	}
	
}
